﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using RTUDataAcquisition.AppCode;
using RTUDataAcquisition.Model;

namespace RTUDataAcquisition.DataAccess
{
    public static class DataBusiness
    {
        /// <summary>
        /// 检查表是否存在
        /// </summary>
        /// <param name="strDBSource">主机或IP地址</param>
        /// <param name="strDBPort">端口</param>
        /// <param name="strDBName">数据库名</param>
        /// <param name="strUser">用户名</param>
        /// <param name="strPassWord">密码</param>
        /// <param name="strTableName">数据表名</param>
        /// <returns>true,false</returns>
        public static bool CheckDataTable(string strDBSource, string strDBPort, string strDBName, string strUser, string strPassWord, string strTableName)
        {
            try
            {
                string strTemp = strDBPort != string.Empty ? strDBSource + "," + strDBPort : strDBSource;
                string strConn = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};Connection Timeout=2;"
                , strTemp, strDBName, strUser, strPassWord);
                string strSql = string.Format("select name from sysobjects where name = '{0}'", strTableName);

                if (!DbHelper.ConnectionTest(strConn))
                {
                    Messages.ShowMessage("测试连接数据库失败，请检查数据库设置!");
                    return false;
                }

                if (DbHelper.Query(strSql, strConn).Tables[0].Rows.Count > 0)
                {
                    Messages.ShowMessage("测试数据表成功!");
                    return true;
                }
                else
                {
                    Messages.ShowMessage("测试数据表不存在!");
                    return false;
                }
            }
            catch (Exception ex)
            {
                Messages.SaveLogDialog(ex, "测试数据表失败!");
                return false;
            }
        }

        /// <summary>
        /// 获取所有的要素配置
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetEssConfig()
        {
            try
            {
                //string strSql = String.Format("Select EssCode,EssAscllCode,EssName,EssUnit,EssMinValue,EssMaxValue,EssFfieldName FROM EssConfig where SeqNo={0}", INIConfig.IpPort);
                string strSql = String.Format("Select EssCode,EssAscllCode,EssName,EssUnit,EssMinValue,EssMaxValue,EssFfieldName FROM EssConfig where SeqNo={0}", "1204");
                //Messages.SaveLog(strSql);
                //Messages.SaveLog(INIConfig.StrConfigConn);
                return DbHelper.Query(strSql, INIConfig.StrConfigConn).Tables[0];
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex,ELogLayer.DB);
                return null;
            }
        }

        /// <summary>
        /// 获取报警配置
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetAlarmConfig()
        {
            try
            {
                string strSql = @"Select AlarmName FROM AlarmConfig order by ID";
                return DbHelper.Query(strSql, INIConfig.StrConfigConn).Tables[0];
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return null;
            }
        }

        /// <summary>
        /// 查询设备状态
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetStationStatus()
        {
            try
            {
                string strSql = @"Select  StationNo, StationName, Status, Alarm, RealData,'' as RemoteTask, WaterLevel1, WaterLevel2, TotalRainfall, CurrentRainfall, 
                                            Voltage, Signal, StationType, PacketCount, LastTime FROM StationStatus order by StationNo";
                return DbHelper.Query(strSql, INIConfig.StrConfigConn).Tables[0];
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return null;
            }
        }
        /// <summary>
        /// 查询设备是否转发
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetTransDevice()
        {
            try
            {
                string strSql = @"select st from [TransDevice].[dbo].[water_TRANS]";
                return DbHelper.Query(strSql, INIConfig.StrTransConn).Tables[0];
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return null;
            }
        }
        /// <summary>
        /// 查询设备状态
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetStationStatus(string strSqlCondition)
        {
            try
            {
                //string strSql = @"Select  a.StationNo, a.StationName, case when DATEDIFF ( n , a.LastTime , GETDATE() ) >= isnull(c.TimeoutDrop,120) then '离线' else '在线' end as [Status] , Alarm, RealData, WaterLevel1, WaterLevel2, TotalRainfall, CurrentRainfall, 
                //                            Voltage, Signal, StationType, PacketCount, LastTime FROM StationStatus a left join Config.dbo.StationConfig c on a.StationNo = c.StationNo " + strSqlCondition + " order by a.StationNo";
                string strSql = @"Select  StationNo, StationName, CONVERT(varchar(100), LastTime, 20) as LastTime,case when LastTime is null or DATEDIFF (n,LastTime,GETDATE()) >= isnull(TimeoutDrop,120) then '离线' else '在线' end as [Status] FROM StationConfig " + strSqlCondition + " order by StationNo";
                return DbHelper.Query(strSql, INIConfig.StrConfigConn).Tables[0];
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return null;
            }
        }

        /// <summary>
        /// 查询设备状态
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetStationStatusNew(string strSqlCondition)
        {
            try
            {
                string strSql = string.Format(@"SELECT a.ST as StationNo,a.StationName,case when a.Times >= isnull(a.TimeoutDrop,120) then '离线' else '在线' end as [Status] ,'' as Alarm,'' as RealData,'' as RemoteTask,
                                                                cast(a.Z as float) as WaterLevel1,cast(a.ZB as float) as WaterLevel2 ,cast(a.PT as float) as TotalRainfall,cast(a.PJ as float) as CurrentRainfall,cast(a.VT as float) as Voltage,cast(cast(a.CSIGNAL as float) as varchar) + '%' as Signal,b.TypeName as StationType,a.SERIAL as PacketCount,CONVERT(varchar(100),a.TT,20) as LastTime FROM
                                                                    (SELECT ST,cast(StationName as varchar) as StationName, TELTYPE, SERIAL, TT, [DATA], ESS,DATEDIFF ( n , TT , GETDATE() ) as Times,TimeoutDrop FROM {0}.dbo.RealData a left join Config.dbo.StationConfig c on ST = StationNo {1}) as t 
                                                                pivot(MAX([DATA]) FOR ESS IN(ZT, Z, ZB, PT, PJ, VT, CSIGNAL))a left join Config.dbo.StationType b on a.TELTYPE = ASCII(b.AsciiCode)", INIConfig.RealDataDBName, strSqlCondition);
                return DbHelper.Query(strSql, INIConfig.StrConfigConn).Tables[0];
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return null;
            }
        }

        /// <summary>
        /// 查询设备配置
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetStationConfig(string strSqlCondition)
        {
            try
            {
                string strSql = @"Select  StationNo, StationName, TypeNo, GroupNo, TimeoutDrop FROM StationConfig " + strSqlCondition + " order by StationNo";
                return DbHelper.Query(strSql, INIConfig.StrConfigConn).Tables[0];
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return null;
            }
        }

        /// <summary>
        /// 新增设备要素数据
        /// </summary>
        ///<param name="essDataModel">要素数据MODEL</param>
        /// <returns></returns>
        public static int InserEssData(EssDataModel essDataModel)
        {
            try
            {
                string strSql =  @"INSERT INTO [" + essDataModel.ST + "] VALUES('" + essDataModel.ST + "','" + essDataModel.TT + "','" + essDataModel.TYPE + "'," + essDataModel.SERIAL + "," 
                    + essDataModel.TELTYPE + ",'" + essDataModel.ESS + "','" + essDataModel.SDATA + "'," + essDataModel.DATA + ",'" + essDataModel.UT + "','" + essDataModel.STT + "','" + essDataModel.UNTT + "')";
                return DbHelper.ExecuteSql(strSql, INIConfig.GetDBConnectionString());
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return 0;
            }
        }

        /// <summary>
        /// 新增设备要素实时数据
        /// </summary>
        ///<param name="essDataModel">要素数据MODEL</param>
        /// <returns></returns>
        public static int InserRealData(EssDataModel essDataModel)
        {
            try
            {
                string strSql = @"INSERT INTO RealData VALUES('" + essDataModel.ST + "','" + essDataModel.TT + "','" + essDataModel.TYPE + "'," + essDataModel.SERIAL + ","
                    + essDataModel.TELTYPE + ",'" + essDataModel.ESS + "','" + essDataModel.SDATA + "'," + essDataModel.DATA + ",'" + essDataModel.UT + "','" + essDataModel.STT + "','" + essDataModel.UNTT + "')";
                return DbHelper.ExecuteSql(strSql, INIConfig.StrRealdataConn);
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return 0;
            }
        }

        /// <summary>
        /// 新增设备要素历史数据
        /// </summary>
        ///<param name="essDataModel">要素数据MODEL</param>
        /// <returns></returns>
        public static int InserHisData(EssDataModel essDataModel, string strConn)
        {
            try
            {
                string strSql = @"INSERT INTO [" + essDataModel.ST + "] VALUES('" + essDataModel.ST + "','" + essDataModel.TT + "','" + essDataModel.TYPE + "'," + essDataModel.SERIAL + ","
                    + essDataModel.TELTYPE + ",'" + essDataModel.ESS + "','" + essDataModel.SDATA + "'," + essDataModel.DATA + ",'" + essDataModel.UT + "','" + essDataModel.STT + "',NULL)";
                return DbHelper.ExecuteSql(strSql, strConn);
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return 0;
            }
        }
        // 查看图片是否已存在
        public static int SelectImageData(string ST,string TT,string ESS, string strConn)
        {
            try
            {
                string strSql = @"select * from  [" +ST + "] where TT='" + TT + "' and ess='" + ESS + "'";
                //Messages.SaveLog(strSql);
                DataSet result = DbHelper.Query(strSql, strConn);
                int rowCount = result.Tables[0].Rows.Count;
                //Messages.SaveLog(rowCount.ToString());
                return rowCount;
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return 0;
            }
        }

        /// <summary>
        /// 删除设备要素实时数据
        /// </summary>
        ///<param name="essDataModel">要素数据MODEL</param>
        /// <returns></returns>
        public static bool DeleteRealData(string strStationNo)
        {
            try
            {
                string strSql = string.Format("Delete RealData where ST='{0}'",strStationNo);
                return DbHelper.ExecuteSql(strSql, INIConfig.StrRealdataConn) > 0?true:false;
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return false;
            }
        }
        public static bool DeleteRealData_R(string strStationNo)
        {
            try
            {
                string strSql = string.Format("Delete RealData where ST='{0}'", strStationNo);
                return DbHelper.ExecuteSql(strSql, INIConfig.StrRealdataConn_R) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return false;
            }
        }
        /// <summary>
        /// 新增站点状态
        /// </summary>
        /// <param name="strStationNo">站点编号</param>
        /// <param name="listModel">要素实时数据model</param>
        /// <returns>true,false</returns>
        public static bool InsertStationStatus(string strStationNo, List<EssDataModel> listModel)
        {
            try
            {
                string strEssData = GetStationEssDataToString(listModel);
                string strAlarmInfo = GetAlarmToString(listModel);
                string strSql = string.Format(@"delete Config.dbo.StationStatus where StationNo='{0}'
                                            insert into Config.dbo.StationStatus 
                                           SELECT a.ST,c.StationName,case when a.Times >= isnull(c.TimeoutDrop,120) then '离线' else '在线' end as [Status] ,'{3}' as ZT,'{2}' as RealData,
                                            cast(a.Z as float) as Z,cast(a.ZB as float) as ZB ,cast(a.PT as float) as PT,cast(a.PJ as float) as PJ,cast(a.VT as float) as VT,cast(cast(a.CSIGNAL as float) as varchar) + '%' as CSIGNAL,b.TypeName as TELTYPE,a.SERIAL,CONVERT(varchar(100),a.TT,20) as TT FROM
                                                (SELECT ST, TELTYPE, SERIAL, TT, [DATA], ESS,DATEDIFF ( n , TT , GETDATE() ) as Times FROM {1}.dbo.RealData where ST='{0}' ) as t 
                                          pivot(MAX([DATA]) FOR ESS IN(ZT, Z, ZB, PT, PJ, VT, CSIGNAL))a left join Config.dbo.StationType b on a.TELTYPE = ASCII(b.AsciiCode) left join Config.dbo.StationConfig c on a.ST = c.StationNo", strStationNo,INIConfig.RealDataDBName,strEssData, strAlarmInfo);
                return DbHelper.ExecuteSql(strSql, INIConfig.GetDBConnectionString()) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return false;
            }
        }

        /// <summary>
        /// 新增站点配置
        /// </summary>
        /// <param name="model">站点信息</param>
        /// <returns>true,false</returns>
        public static bool InsertStationConfig(EssDataModel model)
        {
            try
            {
                string strSql = string.Format(@" if not exists(select StationNo from StationConfig where StationNo = '{0}')
                                                                 begin insert into StationConfig (StationNo,StationName,TypeNo,GroupNo,LastTime,TimeoutDrop, LowVoltageAlarm,HighVoltageAlarm,LowWaterLevelAlarm,Turbidity) values('{0}', null, {1}, null, '{2}',120, null, null, null, null) end
                                                                    else begin update  StationConfig set TypeNo={1},LastTime='{2}' where StationNo = '{0}'  and LastTime<'{2}' end", model.ST, model.TELTYPE, model.TT);
                return DbHelper.ExecuteSql(strSql, INIConfig.StrConfigConn) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return false;
            }
        }
        public static bool InsertStationConfig_R(EssDataModel model)
        {
            try
            {
                string strSql = string.Format(@" if not exists(select StationNo from StationConfig where StationNo = '{0}')
                                                                 begin insert into StationConfig (StationNo,StationName,TypeNo,GroupNo,LastTime,TimeoutDrop, LowVoltageAlarm,HighVoltageAlarm,LowWaterLevelAlarm,Turbidity) values('{0}', null, {1}, null, '{2}',120, null, null, null, null) end
                                                                    else begin update  StationConfig set TypeNo={1},LastTime='{2}' where StationNo = '{0}'  and LastTime<'{2}' end", model.ST, model.TELTYPE, model.TT);
                return DbHelper.ExecuteSql(strSql, INIConfig.StrConfigConn_R) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return false;
            }
        }
        /// <summary>
        /// 修改站点配置
        /// </summary>
        /// <param name="strStationNo">站点编号</param>
        /// <param name="strStationName">名称</param>
        /// <param name="intTypeNo">类型</param>
        /// <param name="intGroupNo">分组</param>
        /// <param name="intTimeOut">超时时间</param>
        /// <returns>true,false</returns>
        public static bool UpdateStationConfig(string strStationNo, string strStationName, int intTypeNo, int intGroupNo, int intTimeOut)
        {
            try
            {
                string strSql = string.Format("update StationConfig set StationName='{1}', TypeNo={2}, GroupNo={3}, TimeoutDrop={4} where StationNo='{0}'", strStationNo,strStationName,intTypeNo,intGroupNo,intTimeOut);
                return DbHelper.ExecuteSql(strSql, INIConfig.StrConfigConn) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return false;
            }
        }

        /// <summary>
        /// 删除站点配置
        /// </summary>
        /// <param name="strStationNo">站点编号</param>
        /// <returns>true,false</returns>
        public static bool DeleteStationConfig(string strStationNo)
        {
            try
            {
                string strSql = string.Format("delete StationConfig where StationNo='{0}'", strStationNo);
                return DbHelper.ExecuteSql(strSql, INIConfig.StrConfigConn) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return false;
            }
        }

        /// <summary>
        /// 查询站点设备要素数据
        /// </summary>
        /// <param name="strStationNo">站点编号</param>
        /// <param name="strTime">设备上传时间</param>
        /// <returns>DataTable</returns>
        public static DataTable GetStationEssData(string strStationNo, string strTime)
        {
            try
            {
                string strSql = string.Format(@"SELECT b.EssName,cast(a.[DATA] as float) as [DATA],b.EssUnit,a.TT,b.EssAscllCode FROM [{0}] a left join Config.dbo.EssConfig b on b.SeqNo={2} and a.ESS=b.EssAscllCode where a.TT='{1}' order by ID", strStationNo, strTime, INIConfig.IpPort);
                return DbHelper.Query(strSql, INIConfig.GetDBConnectionString()).Tables[0];
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return null;
            }
        }

        /// <summary>
        /// 检查数据是否重复
        /// </summary>
        /// <param name="strStationNo">站点编号</param>
        /// <param name="strTime">设备上传时间</param>
        /// <param name="strType">数据类型</param>
        /// <param name="serial">流水号</param>
        /// <returns></returns>
        public static bool CheckData(string strStationNo, string strTime, string strType, int serial)
        {
            try
            {
                string strSql = string.Format(@"SELECT count(*) FROM [{0}] where  TT='{1}' and TYPE='{2}' and SERIAL={3}", strStationNo, strTime, strType, serial);
                DataTable dt = DbHelper.Query(strSql, INIConfig.GetDBConnectionString()).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0][0]) > 0 ? true : false;
                }
                return false;
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return false;
            }
        }

        /// <summary>
        /// 查询站点设备要素数据转成实时数据字符型
        /// </summary>
        /// <param name="strStationNo">站点编号</param>
        /// <param name="strTime">设备上传时间</param>
        /// <returns>string</returns>
        public static string GetStationEssDataToString(string strStationNo, string strTime, ref string strAlarmValue)
        {
            try
            {
                string strEssData = string.Empty;
                DataTable dt = GetStationEssData(strStationNo, strTime);
                if (dt != null && dt.Rows.Count > 0)
                {
                     DataRow[] dr = dt.Select("EssAscllCode='ZT'");
                    if (dr.Length > 0)
                    {
                        strAlarmValue = dr[0]["DATA"].ToString();
                    }
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if (i == 128)
                        {
                            break;
                        }
                        strEssData += dt.Rows[i][0].ToString() + ":" + dt.Rows[i][1].ToString() + " ";
                    }
                }
                return strEssData;
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return string.Empty;
            }
        }

        /// <summary>
        /// 查询站点设备要素数据转成实时数据字符型
        /// </summary>
        /// <param name="strStationNo">站点编号</param>
        /// <param name="strTime">设备上传时间</param>
        /// <returns>string</returns>
        public static string GetStationEssDataToString(List<EssDataModel> listModel)
        {
            try
            {
                string strEssData = string.Empty;
                for (int i = 0; i < listModel.Count; i++)
                {
                    if (i == 128)
                    {
                        break;
                    }
                    strEssData += listModel[i].ESSName + ":" + listModel[i].DATA.ToString() + " ";
                }
                return strEssData;
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return string.Empty;
            }
        }

        /// <summary>
        /// 获取报警信息
        /// </summary>
        /// <param name="listModel">要素实时数据model</param>
        /// <returns>string</returns>
        public static string GetAlarmToString(List<EssDataModel> listModel)
        {
            string strAlarmValue = string.Empty;
            List<EssDataModel> essModel = listModel.Where<EssDataModel>(x => x.ESS == "ZT").ToList<EssDataModel>();
            if (essModel.Count > 0)
            {
                strAlarmValue = DataBusiness.GetAlarmToString(essModel[0].DATA.ToString());
            }
            StringBuilder sbValue = new StringBuilder();
            if (strAlarmValue != string.Empty)
            {
                StringBuilder sb = new StringBuilder();
                byte[] bytes = TransData.ConvertHexStringToBytes(strAlarmValue.PadLeft(8, '0'));
                for (int i = 0; i < bytes.Length; i++)
                {
                    sb.Append(Convert.ToString(bytes[i], 2).PadLeft(8, '0'));
                }
                if (MainForm.dtAlarmConfig != null)
                {
                    string strTemp = sb.ToString();
                    for (int m = 0; m < MainForm.dtAlarmConfig.Rows.Count; m++)
                    {
                        if (MainForm.dtAlarmConfig.Rows.Count < strTemp.Length)
                        {
                            if (strTemp[m].ToString() == "1")
                            {
                                sbValue.Append(MainForm.dtAlarmConfig.Rows[m][0].ToString() + "  ");
                            }
                        }
                    }
                }
            }
            return sbValue.ToString();
        }
        /// <summary>
        /// 获取报警信息
        /// </summary>
        /// <param name="strAlarmValue">报警状态</param>
        /// <returns>string</returns>
        public static string GetAlarmToString(string strAlarmValue)
        {
            StringBuilder sbValue = new StringBuilder();
            if (strAlarmValue != string.Empty)
            {
                StringBuilder sb = new StringBuilder();
                byte[] bytes = TransData.ConvertHexStringToBytes(strAlarmValue.PadLeft(8, '0'));
                for (int i = 0; i < bytes.Length; i++)
                {
                    sb.Append(Convert.ToString(bytes[i], 2).PadLeft(8, '0'));
                }
                if (MainForm.dtAlarmConfig != null)
                {
                    string strTemp = sb.ToString();
                    for (int m = 0; m < MainForm.dtAlarmConfig.Rows.Count; m++)
                    {
                        if (MainForm.dtAlarmConfig.Rows.Count < strTemp.Length)
                        {
                            if (strTemp[m].ToString() == "1")
                            {
                                sbValue.Append(MainForm.dtAlarmConfig.Rows[m][0].ToString() + "  ");
                            }
                        }
                    }
                }
            }
            return sbValue.ToString();
        }

        /// <summary>
        /// 创建历史数据表
        /// </summary>
        /// <param name="strStationNo">站点编号</param>
        /// <returns>true,false</returns>
        public static int CreateHisDataTable(string strStationNo, string strConn)
        {
            try
            {
                string strSql = string.Format(@"IF not exists(select name from sysobjects where name = '{0}')
                                                                    BEGIN
                                                                    CREATE TABLE [dbo].[{0}](
	                                                                    [ID] [int] IDENTITY(1,1) NOT NULL,
	                                                                    [ST] [varchar](20) NOT NULL,
	                                                                    [TT] [datetime] NOT NULL,
	                                                                    [TYPE] [varchar](20) NOT NULL,
	                                                                    [SERIAL] [int] NOT NULL,
	                                                                    [TELTYPE] [int] NOT NULL,
	                                                                    [ESS] [varchar](20) NOT NULL,
	                                                                    [SDATA] [varchar](max) NOT NULL,
	                                                                    [DATA] [decimal](20, 5) NOT NULL,
	                                                                    [UT] [datetime] NOT NULL,
	                                                                    [STT] [datetime] NOT NULL,
	                                                                    [UNTT] [datetime] NULL,
                                                                     CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED 
                                                                    (
	                                                                    [ID] ASC
                                                                    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                                                                    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

                                                                    CREATE NONCLUSTERED INDEX [ESS] ON [dbo].[{0}]
                                                                    (
	                                                                    [ESS] ASC
                                                                    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

                                                                    CREATE NONCLUSTERED INDEX [SERIAL] ON [dbo].[{0}]
                                                                    (
	                                                                    [SERIAL] ASC,
	                                                                    [ST] ASC
                                                                    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

                                                                    CREATE NONCLUSTERED INDEX [CHECK] ON [dbo].[{0}]
                                                                    (
	                                                                    [TT] ASC,
	                                                                    [TYPE] ASC,
	                                                                    [SERIAL] ASC
                                                                    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

                                                                    CREATE NONCLUSTERED INDEX [TT] ON [dbo].[{0}]
                                                                    (
	                                                                    [TT] ASC
                                                                    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

                                                                    CREATE NONCLUSTERED INDEX [TYPE] ON [dbo].[{0}]
                                                                    (
	                                                                    [TYPE] ASC
                                                                    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                                                                    END", strStationNo);
                //Messages.SaveLog(strSql);
                return DbHelper.ExecuteSql(strSql, strConn);
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return -1;
            }
        }

        /// <summary>
        /// 创建设备数据库数据表
        /// </summary>
        /// <param name="strDBName">数据库名</param>
        /// <returns>true,false</returns>
        public static int CreateDataBase(string strDBName)
        {
            try
            {
                string strSql = string.Format(@"IF not exists(select name From master.dbo.sysdatabases where name='{0}')
                                                                    BEGIN
                                                                    CREATE DATABASE [{0}]
                                                                     CONTAINMENT = NONE
                                                                     ON  PRIMARY 
                                                                    ( NAME = N'{0}', FILENAME = N'{1}\{0}.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
                                                                     LOG ON 
                                                                    ( NAME = N'{0}_log', FILENAME = N'{1}\{0}_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
                                                                    END", strDBName, INIConfig.DBFilePath);
                //Messages.SaveLog(strSql);
                DbHelper.ExecuteSql(strSql, INIConfig.StrConnNoDB);
              return 1;

            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return -1;
            }
        }
        public static int CreateDataBase_R(string strDBName)
        {
            try
            {
                string strSql = string.Format(@"IF not exists(select name From master.dbo.sysdatabases where name='{0}')
                                                                    BEGIN
                                                                    CREATE DATABASE [{0}]
                                                                     CONTAINMENT = NONE
                                                                     ON  PRIMARY 
                                                                    ( NAME = N'{0}', FILENAME = N'{1}\{0}.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
                                                                     LOG ON 
                                                                    ( NAME = N'{0}_log', FILENAME = N'{1}\{0}_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
                                                                    END", strDBName, INIConfig.DBFilePath_R);
                //Messages.SaveLog(strSql);
                DbHelper.ExecuteSql(strSql, INIConfig.StrConnNoDB_R);
                return 1;

            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return -1;
            }
        }
        /// <summary>
        /// 批量新增实时或历史数据
        /// </summary>
        /// <param name="listModel">要素数据Model</param>
        /// <param name="strDBTableName">历史表表名</param>
        public static bool InsertRealAndHisData(List<EssDataModel> listModel ,string strDBTableName,int n)
        {
            try
            {
                //DataTable dt = new DataTable();
                //dt.Columns.AddRange(new DataColumn[]{
                //new DataColumn("ID",typeof(Int32)),
                //new DataColumn("ST",typeof(string)),
                //new DataColumn("TT",typeof(string)),
                //new DataColumn("TYPE",typeof(string)),
                //new DataColumn("SERIAL",typeof(int)),
                //new DataColumn("TELTYPE",typeof(int)),
                //new DataColumn("ESS",typeof(string)),
                //new DataColumn("SDATA",typeof(string)),
                //new DataColumn("DATA",typeof(decimal)),
                //new DataColumn("UT",typeof(string)),
                //new DataColumn("STT",typeof(string)),
                //new DataColumn("UNTT",typeof(string))});

                //for (int i = 0; i < listModel.Count; i++)
                //{
                //    DataRow dr = dt.NewRow();
                //    dr[0] = 0;
                //    dr[1] = listModel[i].ST;
                //    dr[2] = listModel[i].TT;
                //    dr[3] = listModel[i].TYPE;
                //    dr[4] = listModel[i].SERIAL;
                //    dr[5] = listModel[i].TELTYPE;
                //    dr[6] = listModel[i].ESS;
                //    dr[7] = listModel[i].SDATA;
                //    dr[8] = listModel[i].DATA;
                //    dr[9] = listModel[i].UT;
                //    dr[10] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");// listModel[i].STT;
                //    dr[11] = DBNull.Value; //DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
                //    dt.Rows.Add(dr);
                //}

                //DbHelper.BulkToDB(dt, "RealData", INIConfig.StrRealdataConn);
                //DbHelper.BulkToDB(dt, "[" + strDBTableName + "]", INIConfig.GetDBConnectionString());
                //return true;

                if (listModel.Count > 0)
                {
                    StringBuilder sb = new StringBuilder();
                    StringBuilder sb1 = new StringBuilder();
                    sb.Append(@"INSERT INTO [" + listModel[0].ST + "] ([ST],[TT],[TYPE],[SERIAL],[TELTYPE],[ESS],[SDATA],[DATA],[UT],[STT],[UNTT]) VALUES");
                    sb1.Append(@"INSERT INTO RealData ([ST],[TT],[TYPE],[SERIAL],[TELTYPE],[ESS],[SDATA],[DATA],[UT],[STT],[UNTT]) VALUES");
                    string strSTT = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
                    for (int i = 0; i < listModel.Count; i++)
                    {
                        string strValues = @"('" + listModel[i].ST + "','" + listModel[i].TT + "','" + listModel[i].TYPE + "'," + listModel[i].SERIAL + ","
                        + listModel[i].TELTYPE + ",'" + listModel[i].ESS + "','" + listModel[i].SDATA + "'," + listModel[i].DATA + ",'" + listModel[i].UT + "','" + strSTT + "',null),";
                        sb.Append(strValues);
                        sb1.Append(strValues);
                    }
                    DbHelper.ExecuteSql(sb.ToString().TrimEnd(new char[] {','}), INIConfig.GetDBConnectionString());
                    DbHelper.ExecuteSql(sb1.ToString().TrimEnd(new char[] { ',' }), INIConfig.StrRealdataConn);
                    if (n >= 1) {
                        DbHelper.ExecuteSql(sb.ToString().TrimEnd(new char[] { ',' }), INIConfig.GetRDBConnectionString());
                        DbHelper.ExecuteSql(sb1.ToString().TrimEnd(new char[] { ',' }), INIConfig.StrRealdataConn_R);
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                Messages.SaveLog(ex, ELogLayer.DB);
                return false;
            }
        }
    }
}
